import xlrd
import pymysql
'''
    解析excel文档数据，存入mysql数据库
'''

def parseexcel(excelfile):
    # 打开Excel文件读取数据
    data = xlrd.open_workbook('F:\户口迁移事项\户籍业务受理地点及联系电话.xlsx')

    # 获取一个sheet
    # table = data.sheets()[1]              #通过索引顺序获取
    # table = data.sheet_by_index(0)        #通过索引顺序获取
    table = data.sheet_by_name('Sheet2')  # 通过名称获取

    # 获取行数
    rows = table.nrows
    # 获取列数
    # table.ncols

    # 获取整行和整列的值（返回数组）
    # 结合行数遍历整个excel表格
    i = 1
    list = []
    while i < rows:
        list_row = table.row_values(i)  # 返回list类型
        list.append(tuple(list_row))
        i += 1
    return list


##第二种表格遍历方式
#     num_rows = table.nrows
#     for curr_row in range(num_rows):
#         row = table.row_values(curr_row)
#         print('row%s is %s' %(curr_row,row))    输出行号和行的内容

def get_connect():
    '''获取mysql数据库连接'''
    dbconn = pymysql.connect("localhost", 'root', '123456', 'test')
    return dbconn


def get_cursor(conn):
    '''获取mysql数据库游标对象'''
    cu = conn.cursor()
    return cu


def initdb(date):
    conn = get_connect()
    cu = get_cursor(conn)
    save_sql = "INSERT INTO HuJiBanJi values (%s, %s, %s,%s, %s, %s,%s)"
    select_sql = "select count(*) from HuJiBanJi where id = %s"
    del_sql = "delete from HuJiBanJi where id = %s"
    for d in date:
        key = d[0]
        cu.execute(select_sql, key)
        flag = cu.fetchone()
        if flag[0] == 1:
            cu.execute(del_sql, key)
        cu.execute(save_sql, d)
    conn.commit()
    conn.close()


def main():
    excelfile = 'F:\户口迁移事项\户籍业务受理地点及联系电话.xlsx'
    date = parseexcel(excelfile)
    print("表格数据解析完成")
    initdb(date)
    print("数据处理成功")


# def main():
#     #获取excel单元格的数据
#     data = xlrd.open_workbook('F:\户口迁移事项\户籍业务受理地点及联系电话.xlsx')
#     table = data.sheet_by_name('Sheet2')
#
#     #相当于表格是一个二维数据，可以使用cell在表格中按照下标取任意数据
#     print(table.cell(0,4).value)

if __name__ == '__main__':
    main()

